﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Data.Common;
using System.Xml;
using System.Xml.Serialization;
using System.IO;

namespace MvcApplicationYoung.Models
{
    public class MyDBEnts
    { 
        MyDBEntities myContext = null;
        string connStr = string.Empty;

        public enum SingleTable
        {
            departments,
            users,
            employees,
            datalists
        }

        public MyDBEnts()
        {
            connStr = GetConnStringBuilder().ToString();
        }

        private MyDBEntities CreateEFCon()
        {
            try
            {
                MyDBEntities context = new MyDBEntities(new EntityConnection(connStr));
                context.ContextOptions.LazyLoadingEnabled = false;
                return context;
            }
            catch(Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return null;
            }
        }

        private EntityConnectionStringBuilder GetConnStringBuilder()
        { // Specify the provider name, server and database.
            string providerName = "System.Data.SqlClient";
            string serverName = "YOUNG-PC";
            string databaseName = "mydb";

            // Initialize the connection string builder for the
            // underlying provider.
            SqlConnectionStringBuilder sqlBuilder =
                new SqlConnectionStringBuilder();

            // Set the properties for the data source.
            sqlBuilder.DataSource = serverName;
            sqlBuilder.InitialCatalog = databaseName;
            sqlBuilder.IntegratedSecurity = false;
            sqlBuilder.UserID = "sa";
            sqlBuilder.Password = "197839yj";
            sqlBuilder.MultipleActiveResultSets = true;

            // Build the SqlConnection connection string.
            string providerString = sqlBuilder.ToString();

            // Initialize the EntityConnectionStringBuilder.
            EntityConnectionStringBuilder entityBuilder =
                new EntityConnectionStringBuilder();

            //Set the provider name.
            entityBuilder.Provider = providerName;

            // Set the provider-specific connection string.
            entityBuilder.ProviderConnectionString = providerString;

            // Set the Metadata location.
            entityBuilder.Metadata = @"res://*/AllBasicObjects.csdl|
                            res://*/AllBasicObjects.ssdl|
                            res://*/AllBasicObjects.msl";

            return entityBuilder;
        }

        private void SetMenuXMLNode(XmlDocument doc, XmlNode xn, int currentMenuID)
        {
            string esqlQuery = @"SELECT VALUE a FROM MyDBEntities.menues AS a WHERE a.parentMenu == @ln1 ORDER BY a.sequenceInLevel";
            ObjectQuery<menue> query = myContext.CreateQuery<menue>(esqlQuery, new ObjectParameter("ln1", currentMenuID));

            foreach (menue rec in query)
            {
                XmlNode singleXn = doc.CreateElement("MenuNode");
                xn.AppendChild(singleXn);
                XmlElement menuIDNode = doc.CreateElement("menuID");
                menuIDNode.InnerText = rec.menuID.ToString();

                XmlElement menuNameNode = doc.CreateElement("menuName");
                menuNameNode.InnerText = rec.menuName.ToString();

                XmlElement tagNode = doc.CreateElement("tag");
                tagNode.InnerText = rec.tag.ToString();

                XmlElement parentMenuNode = doc.CreateElement("parentMenu");
                parentMenuNode.InnerText = rec.parentMenu.ToString();

                XmlElement toolTipNode = doc.CreateElement("toolTip");
                toolTipNode.InnerText = rec.toolTip.ToString();

                singleXn.AppendChild(menuIDNode);
                singleXn.AppendChild(menuNameNode);
                singleXn.AppendChild(tagNode);
                singleXn.AppendChild(parentMenuNode);
                singleXn.AppendChild(toolTipNode);

                SetMenuXMLNode(doc, singleXn, rec.menuID);
            }
        }

        public string GetMenuesXMLJson()
        {
            XmlDocument doc = new XmlDocument();

            XmlNode singleXn = doc.CreateElement("MenuNodes");
            doc.AppendChild(singleXn);

            string jsonString = string.Empty;
            using (myContext = CreateEFCon())
            {
                SetMenuXMLNode(doc, singleXn, 0);
                jsonString = ModelsCommon.XMLToJSON(doc);
            }

            return jsonString;
        }

        public string GetTableJson(string objectID, SingleTable st)
        {
            string jsonString = string.Empty;
            MemoryStream ms = new MemoryStream();
            object query = null;
            string esqlQuery = string.Empty;
            if (string.IsNullOrEmpty(objectID))
            {
                esqlQuery = @"SELECT VALUE a FROM MyDBEntities."
            + Enum.GetName(typeof(SingleTable), st)
            + " AS a";
            }

            using (myContext = CreateEFCon())
            {
                switch (st)
                {
                    case SingleTable.departments:
                        if (string.IsNullOrEmpty(esqlQuery))
                        {
                            esqlQuery = @"SELECT VALUE a FROM MyDBEntities."
                            + Enum.GetName(typeof(SingleTable), st)
                            + " AS a"
                            + " WHERE a.departmentName LIKE '%" + objectID + "%'";
                        }

                        ObjectQuery<department> query1;
                        query1= new ObjectQuery<department>(esqlQuery, myContext);
                        if (query1.Count<department>() >= 0)
                        { query = query1; }
                        else
                        { return ""; }
                        break;
                    case SingleTable.employees:
                        if (string.IsNullOrEmpty(esqlQuery))
                        {
                            esqlQuery = @"SELECT VALUE a FROM MyDBEntities."
                                                 + Enum.GetName(typeof(SingleTable), st)
                                                 + " AS a"
                                                 + " WHERE a.name LIKE '%" + objectID + "%'";
                        }


                        ObjectQuery<employee> query2 = new ObjectQuery<employee>(esqlQuery, myContext);
                        if (query2.Count<employee>() >= 0)
                        { query = query2; }
                        else
                        { return ""; }
                        break;
                    default:
                        break;
                }
                jsonString = ModelsCommon.XMLToJSON(query);
            }

            return jsonString;

        }

        private string AddDepartmentRow(string modifyDepartmentRow)
        {
            try
            {
                string[] modifyDepartment = modifyDepartmentRow.Split(new string[] { "," }, StringSplitOptions.None);
                using (myContext = CreateEFCon())
                {
                    department de = new department();
                    //em.employeeID=7;
                    de.departmentName = modifyDepartment[1];
                    de.departmentTotal = int.Parse(modifyDepartment[2]);
                    de.departmentIntroduction = modifyDepartment[3];
                    myContext.departments.AddObject(de);

                    myContext.SaveChanges();
                    return de.departmentID.ToString();
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false.ToString();
            }
        }

        private string AddEmployeeRow(string modifyEmployeeRow)
        {
            try
            {
                string[] modifyEmployee = modifyEmployeeRow.Split(new string[] { "," }, StringSplitOptions.None);
                using (myContext = CreateEFCon())
                {
                    //string esqlQuery = @"SELECT VALUE a FROM MyDBEntities.employees"
                    //                   + " AS a"
                    //                   + " WHERE a.employeeID=" + modifyEmployee[0] + "";
                    //ObjectQuery<employee> query2 = new ObjectQuery<employee>(esqlQuery, myContext);
                    //foreach (employee em in query2)
                    //{
                    //    myContext.DeleteObject(em);
                        
                    //}
                    
                    employee em=new employee();
                    //em.employeeID=7;
                    em.onDepartment=int.Parse(modifyEmployee[1]);
                    em.name=modifyEmployee[2];
                    em.introduction=modifyEmployee[3];
                    myContext.employees.AddObject(em);

                    myContext.SaveChanges();
                    return em.employeeID.ToString();
                }
            }
            catch (Exception ex)
            {
            System.Diagnostics.Debug.WriteLine(ex.Message);
            return false.ToString();
            }
        }

        private bool DeleteEmployeeRow(string modifyEmployeeRow)
        {
            try
            {
                string[] modifyEmployee = modifyEmployeeRow.Split(new string[] { "," }, StringSplitOptions.None);
                using (myContext = CreateEFCon())
                {
                    string esqlQuery = @"SELECT VALUE a FROM MyDBEntities.employees"
                                       + " AS a"
                                       + " WHERE a.employeeID=" + modifyEmployee[0] + "";
                    ObjectQuery<employee> query2 = new ObjectQuery<employee>(esqlQuery, myContext);
                    foreach (employee em in query2)
                    {
                        myContext.DeleteObject(em);
                    }
                    myContext.SaveChanges();
                }
                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false;
            }
        }

        private bool DeleteDepartmentRow(string modifyDepartmentRow)
        {
            try
            {
                string[] modifyDepartment = modifyDepartmentRow.Split(new string[] { "," }, StringSplitOptions.None);
                using (myContext = CreateEFCon())
                {
                    string esqlQuery = @"SELECT VALUE a FROM MyDBEntities.departments"
                                       + " AS a"
                                       + " WHERE a.departmentID=" + modifyDepartment[0] + "";
                    ObjectQuery<department> query2 = new ObjectQuery<department>(esqlQuery, myContext);
                    foreach (department de in query2)
                    {
                        myContext.DeleteObject(de);
                    }
                    myContext.SaveChanges();
                }
                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false;
            }
        }

        private bool ModifyEmployeeRow(string modifyEmployeeRow)
        {
            try
            {
                string[] modifyEmployee = modifyEmployeeRow.Split(new string[] { "," }, StringSplitOptions.None);
                using (myContext = CreateEFCon())
                {
                    string esqlQuery = @"SELECT VALUE a FROM MyDBEntities.employees"
                                       + " AS a"
                                       + " WHERE a.employeeID=" + modifyEmployee[0] + "";
                    ObjectQuery<employee> query2 = new ObjectQuery<employee>(esqlQuery, myContext);

                    foreach (employee em in query2)
                    {
                        em.onDepartment = int.Parse(modifyEmployee[1]);
                        em.name = modifyEmployee[2];
                        em.introduction = modifyEmployee[3];
                    }
                    myContext.SaveChanges();
                }
                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false;
            }
        }

        private bool ModifyDepartmentRow(string modifyDepartmentRow)
        {
            try
            {
                string[] modifyDepartment = modifyDepartmentRow.Split(new string[] { "," }, StringSplitOptions.None);
                using (myContext = CreateEFCon())
                {
                    string esqlQuery = @"SELECT VALUE a FROM MyDBEntities.departments"
                                       + " AS a"
                                       + " WHERE a.departmentID=" + modifyDepartment[0] + "";
                    ObjectQuery<department> query2 = new ObjectQuery<department>(esqlQuery, myContext);

                    foreach (department de in query2)
                    {
                        de.departmentName = modifyDepartment[1];
                        de.departmentTotal = int.Parse(modifyDepartment[2]);
                        de.departmentIntroduction = modifyDepartment[3];
                        de.parentDepartment = int.Parse(modifyDepartment[4]);
                    }
                    myContext.SaveChanges();
                }
                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false;
            }
        }
        
        public string UpdateDepartmentRow(string commitType, string modifyDepartmentRow)
        {
            switch (commitType)
            {
                case "add":
                    return AddDepartmentRow(modifyDepartmentRow);
                case "modify":
                    return ModifyDepartmentRow(modifyDepartmentRow).ToString();
                case "delete":
                    return DeleteDepartmentRow(modifyDepartmentRow).ToString();
                default:
                    return false.ToString();
            }
        }

        public string UpdateEmployeeRow(string commitType, string modifyEmployeeRow)
        {
            switch (commitType)
            {
                case "add":
                    return AddEmployeeRow(modifyEmployeeRow);
                case "modify":
                    return ModifyEmployeeRow(modifyEmployeeRow).ToString();
                case "delete":
                    return DeleteEmployeeRow(modifyEmployeeRow).ToString();
                default:
                    return false.ToString();
            }
        }
    }
}